#include "mysql.h"

#include <QDir>
#include <QDebug>
#include <QSqlQuery>
#include <QSqlError>

MySql::MySql(QString& Dir)
    : dbDir(Dir)
{
    connectionName = "sqlslite";
    if (!isDirExist())        //若路径不存在则创建该路径//
    {
        QDir backup;
        backup.mkdir(dbDir);
    }

    if (!CreateConnection())
    {
        qDebug() << "can not open db!";
        return;
    }
}

MySql::~MySql()
{
    closeDb();
}


bool MySql::CreateConnection()
{
    db = QSqlDatabase::addDatabase("QSQLITE",connectionName);  //创建一个SQLite数据库连接//
    QString dbName = dbDir + "XinYu.db";                         //本地数据库文件名//
    db.setDatabaseName(dbName);                                //数据库连接命名//
    if (!db.open())                                            //如果.db文件不存在，自动新建.db文件并打开//
    {
        return false;
    }

    QSqlQuery query(QSqlDatabase::database(connectionName));
    query.exec("create table DataInfo(ID int primary key,UserID varchar(50) NOT NULL,UserName varchar(20),UserAge varchar(10),UserSex varchar(10),UserHeight varchar(20),UserWeight varchar(20),MeasureTime varchar(20),ReMarks varchar(20),hrvIndexResults varchar(500000))");
    return true;
}


bool MySql::isDirExist()
{
    QDir temp_dir(dbDir);
    return temp_dir.exists();
}

void MySql::closeDb()
{
    if (db.isOpen())
    {
        db.close();
    }
}
//UserID varchar(50) NOT NULL,
//UserName varchar(20),
//UserAge varchar(10),
//UserSex varchar(10),
//UserHeight varchar(20),
//UserWeight varchar(20),
//MeasureTime varchar(20),
//ReMarks varchar(20),
//hrvIndexResults varchar(500000)
bool MySql::MyInsert(DataBeans& dataBean)
{
    requestAccessToken();

    QSqlQuery query(QSqlDatabase::database(connectionName));
    query.prepare("insert into DataInfo(UserID,UserName,UserAge,UserSex,UserHeight,UserWeight,MeasureTime,ReMarks,hrvIndexResults)"
        "values(:UserID,:UserName,:UserAge,:UserSex,:UserHeight,:UserWeight,:MeasureTime,:ReMarks,:hrvIndexResults)");
    query.bindValue(":UserID",dataBean.UserID);
    query.bindValue(":UserName",dataBean.UserName);
    query.bindValue(":UserAge",dataBean.UserAge);
    query.bindValue(":UserSex",dataBean.UserSex);
    query.bindValue(":UserHeight",dataBean.UserHeight);
    query.bindValue(":UserWeight",dataBean.UserWeight);
    query.bindValue(":MeasureTime",dataBean.MeasureTime);
    query.bindValue(":ReMarks",dataBean.ReMarks);
    query.bindValue(":hrvIndexResults",dataBean.hrvIndexResults);
    if (!query.exec())
    {
        qDebug() << "/*************/" << "false" ;
        return false;
    }

    releaseAccessToken();
    qDebug() << "/*************/" << "Ok" ;
    return true;


}

bool MySql::MySelect(QString userID,DataBeans& dataBean)
{
    requestAccessToken();
    QSqlQuery query(QSqlDatabase::database(connectionName));
    query.prepare("select * from DataInfo where UserID = :UserID");
    query.bindValue(":UserID",userID);
    if (!query.exec())
    {
        return false;
    }
    query.next();
    dataBean.UserID = query.value(1).toString();
    dataBean.UserName = query.value(2).toString();
    dataBean.UserAge = query.value(3).toString();
    dataBean.UserSex = query.value(4).toString();
    dataBean.UserHeight = query.value(5).toString();
    dataBean.UserWeight = query.value(6).toString();
    dataBean.MeasureTime=query.value(7).toString();
    dataBean.ReMarks = query.value(8).toString();
    dataBean.hrvIndexResults=query.value(9).toString();
    releaseAccessToken();

    return true;
}
bool MySql::Query(QString userID)
{
    requestAccessToken();
    bool b=false;
    QSqlQuery query(QSqlDatabase::database(connectionName));
    query.prepare("select * from DataInfo where UserID = :UserID");
    query.bindValue(":UserID",userID);
    if (query.exec())
    {
        if(query.next())
            b=true;
    }
    releaseAccessToken();
    return b;
}
bool MySql::MyDelete(QString userID)
{
    requestAccessToken();
    QSqlQuery query(QSqlDatabase::database(connectionName));
    query.prepare("delete from DataInfo where UserID = :UserID");
    query.bindValue(":UserID",userID);

    if (!query.exec())
    {
        return false;
    }

    releaseAccessToken();
    return true;
}

bool MySql::MyUpdate(DataBeans& userInfo)
{
    requestAccessToken();
    QSqlQuery query(QSqlDatabase::database(connectionName));
    //UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
    //:UserID,:UserName,:UserAge,:UserSex,:UserHeight,:UserWeight,:MeasureTime,:ReMarks,:hrvIndexResults
    query.prepare("update DataInfo set UserID=:UserID,UserName=:UserName ,UserAge=:UserAge,UserSex=:UserSex,UserHeight=:UserHeight ,UserWeight=:UserWeight,MeasureTime=:MeasureTime ,ReMarks=:ReMarks ,hrvIndexResults=:hrvIndexResults where UserID=:UserID");
    query.bindValue(":UserID",userInfo.UserID);
    query.bindValue(":UserName",userInfo.UserName);
    query.bindValue(":UserAge",userInfo.UserAge);
    query.bindValue(":UserSex",userInfo.UserSex);
    query.bindValue(":UserHeight",userInfo.UserHeight);
    query.bindValue(":UserWeight",userInfo.UserWeight);
    query.bindValue(":MeasureTime",userInfo.MeasureTime);
    query.bindValue(":ReMarks",userInfo.ReMarks);
    query.bindValue(":hrvIndexResults",userInfo.hrvIndexResults);
    if (!query.exec())
    {
        releaseAccessToken();
        return false;
    }else
    {
    releaseAccessToken();
    return true;
    }
}


void  MySql::requestAccessToken(void)
{
    mutex.lock();                           //如果已经lock且还未unlock，一直等待，直到unlock，然后lock//
}

void  MySql::releaseAccessToken(void)
{
    mutex.unlock();
}
QList<DataBeans> MySql::QueryAll()
{
    requestAccessToken();
    QList<DataBeans>uibs;
    DataBeans dataBean;
    QSqlQuery query(QSqlDatabase::database(connectionName));
    query.prepare("select * from DataInfo");
    if(query.exec()){
        while(query.next()){
            dataBean.UserID = query.value(1).toString();
            dataBean.UserName = query.value(2).toString();
            dataBean.UserAge = query.value(3).toString();
            dataBean.UserSex = query.value(4).toString();
            dataBean.UserHeight = query.value(5).toString();
            dataBean.UserWeight = query.value(6).toString();
            dataBean.MeasureTime=query.value(7).toString();
            dataBean.ReMarks = query.value(8).toString();
            dataBean.hrvIndexResults=query.value(9).toString();
            uibs.append(dataBean);
        }
    }
    releaseAccessToken();
    return uibs;
}

bool MySql::MyDataDelete(QString Measuretime)
{
    requestAccessToken();
    QSqlQuery query(QSqlDatabase::database(connectionName));
    query.prepare("delete from DataInfo where MeasureTime = :MeasureTime");
    query.bindValue(":MeasureTime",Measuretime);

    if (!query.exec())
    {
        return false;
    }

    releaseAccessToken();
    return true;
}
QList<DataBeans> MySql::DataQueryBy(QString ID,QString MeasureTime1,QString MeasureTime2)
{
    requestAccessToken();
    QList<DataBeans>dibs;
    DataBeans dataBean;
    QSqlQuery query(QSqlDatabase::database(connectionName));
    if(ID.size()>0)
    {
        if(MeasureTime1.size()>0 && MeasureTime2.size()>0){
            QString dd=QString("select * from DataInfo where MeasureTime BETWEEN '%1 00:00:00' AND '%2 23:59:59'").arg(MeasureTime1,MeasureTime2);
            query.prepare(dd+" AND UserID=:UserID");
            query.bindValue(":UserID",ID);
        }else{
            QString dd=QString("select * from DataInfo where ");
            query.prepare(dd+"UserID=:UserID");
            query.bindValue(":UserID",ID);
        }

    }else
    {
        QString dd=QString("select * from DataInfo where MeasureTime BETWEEN '%1 00:00:00' AND '%2 23:59:59'").arg(MeasureTime1,MeasureTime2);
        query.prepare(dd);
    }

    if(query.exec()){
        while(query.next()){
            dataBean.UserID = query.value(1).toString();
            dataBean.UserName = query.value(2).toString();
            dataBean.UserAge = query.value(3).toString();
            dataBean.UserSex = query.value(4).toString();
            dataBean.UserHeight = query.value(5).toString();
            dataBean.UserWeight = query.value(6).toString();
            dataBean.MeasureTime=query.value(7).toString();
            dataBean.ReMarks = query.value(8).toString();
            dataBean.hrvIndexResults=query.value(9).toString();
            dibs.append(dataBean);
        }
    }
    releaseAccessToken();
    return dibs;
}
DataBeans MySql::DataQueryByMeasureTime(QString MeasureTime)
{
    requestAccessToken();
    DataBeans dataBean;
    QSqlQuery query(QSqlDatabase::database(connectionName));
    QString dd=QString("select * from DataInfo where MeasureTime=:MeasureTime");
    query.prepare(dd);
    query.bindValue(":MeasureTime",MeasureTime);

    if(query.exec()){
        while(query.next()){
            dataBean.UserID = query.value(1).toString();
            dataBean.UserName = query.value(2).toString();
            dataBean.UserAge = query.value(3).toString();
            dataBean.UserSex = query.value(4).toString();
            dataBean.UserHeight = query.value(5).toString();
            dataBean.UserWeight = query.value(6).toString();
            dataBean.MeasureTime=query.value(7).toString();
            dataBean.ReMarks = query.value(8).toString();
            dataBean.hrvIndexResults=query.value(9).toString();
        }
    }
    releaseAccessToken();
    return dataBean;
}
